import pandas as pd
import pymysql

db = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
cursor = db.cursor()


def insertFileSql():
    data = pd.read_excel('C:\\Users\\asus\\Desktop\\没有附件的计划87.xlsx', sheet_name='Sheet2',
                         usecols=['production_code', 'image_plan'])
    for index, row in data.iterrows():
        newSql = """
            SELECT
                att.attid
            FROM
            hi_kc_production_plan plan
                LEFT JOIN hi_kc_process_trajectory tr ON plan.id = tr.production_plan_id 
                left join kc_process_att_relation att on att.trid = tr.id 
            WHERE
                plan.production_code = '{0}' 
                AND tr.process_name = '签署入库回单'
        """.format(row['image_plan'])

        tridSql = """
            SELECT
                tr.id
            FROM
                kc_production_plan plan
                LEFT JOIN kc_process_trajectory tr ON plan.id = tr.production_plan_id 
            WHERE
                plan.production_code = '{0}' 
                AND tr.process_name = '签署入库回单'
        """.format(row['production_code'])

        cursor.execute(tridSql)
        first_row = cursor.fetchone()
        if first_row is not None:
            trid = first_row[0]
        else:
            # Handle the case when no data is returned
            trid = None

        cursor.execute(newSql)
        all_error = cursor.fetchall()
        for error in all_error:
            inserSql = "INSERT INTO `kc_process_att_relation` ( `trid`, `dirid`, `attid`, `delflag`, `createBy`, " \
                       "`createTime`, `deleteBy`, `deleteTime`) VALUES ( '{0}', '{1}', '{2}', 0, '10616', now(), NULL, " \
                       "NULL); ".format(trid, 1352, error[0])
            cursor.execute(inserSql)
            db.commit()


if __name__ == '__main__':
    insertFileSql()
